package com.yjd.bi.etl.loss.service;

import com.yjd.bi.comm.utils.DbNameUtils;
import com.yjd.bi.etl.comm.BaseService;
import com.yjd.bi.etl.sql.LossUserSqlConstants;
import com.yjd.bi.report.model.BiDatabaseInfoModel;
import com.yjd.bi.report.util.BiUtil;
import com.yjd.comm.util.DateUtil;
import com.yjd.comm.util.FrameUtil;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;

@Component("lossUserStatisticsService")
public class LossUserStatisticsService extends BaseService {

    private static long INTERVAL_1DAY_SECONDS = 86400l; // 每天的时间间隔

    private static long INTERVAL_30DAY_SECONDS = 2592000L; //30天的时间间隔

    private static long INITIALIZATION_TIME = 1512057600L; // 开始统计时间20171201,，该时间点需在yjd_balance_month_yyyyMM表存在以后，否则报错，数据统计会失败
    /**
     * 统计流失用户数，并记录到yjd_user_loss表中
     * @throws Exception
     */
    public void recordLossUserSum() throws Exception {
        BiDatabaseInfoModel model = this.getBiDatabaseInfoModel();
        JdbcTemplate jt = BiUtil.getJdbcTemplate(model);

        recordAllLossUser(model, jt);
    }

    /**
     * 自2017.12.01开始，统计以往的流失用户数据
     */
    private void recordAllLossUser(BiDatabaseInfoModel info, JdbcTemplate jt) {
        Long maxTime = INITIALIZATION_TIME; //如果没有统计数据，默认从20171201开始统计
        Integer maxDate = jt.queryForInt(LossUserSqlConstants.LOSS_USER_STATISTICS_MAXDATE);
        if (maxDate != null && maxDate != 0) {
            maxTime = DateUtil.dateStringToTimestamp(maxDate.toString(), DateUtil.PATTERN_yyyyMMdd);
        }

        List<Object[]> insertList = new ArrayList<Object[]>();
        Object[] model;

        Integer endDate = Integer.valueOf(DateUtil.timestampToDateString(FrameUtil.getTime(), DateUtil.PATTERN_yyyyMMdd));
        Long endTime = DateUtil.dateStringToTimestamp(endDate.toString(), DateUtil.PATTERN_yyyyMMdd);
        while ((maxTime += INTERVAL_1DAY_SECONDS) < endTime) {
            Integer sum = queryLossUserSum(jt, maxTime);
            if (sum == null) break;// 如果返回null,说明查询失败，抛出异常，让job下次再处理
            model = new Object[] {DateUtil.timestampToDateString(maxTime, DateUtil.PATTERN_yyyyMMdd), sum };
            insertList.add(model);
        }
        if (insertList != null && insertList.size() > 0) {
            batchUpdateData(info, LossUserSqlConstants.INSERT_LOSS_USER_SUM_DATA, insertList);
        }
    }

    /**
     *  查询某一天，流失用户总数
     * (30天内无任何投资行为，且账户余额为0，即为流失用户)
     * @return
     * @throws Exception
     */
    private Integer queryLossUserSum(JdbcTemplate jt, Long endTime) {
        Long startTime = endTime - INTERVAL_30DAY_SECONDS;

        String end_date = DateUtil.timestampToDateString(endTime, DateUtil.PATTERN_yyyyMMdd);
        String year_month = DateUtil.timestampToDateString(endTime, DateUtil.PATTERN_yyyyMM);

        //查询 到指定日期为止，流失用户总数
        String QUERY_LOSS_USER_SUM_SQL = "select count(a.user_id) from " + DbNameUtils.get("yjd_balance_month_" + year_month) +
                " a where a.funds_date = ? and a.balance <= 10 " +
                " and not exists (select 1 from " +
                " (select distinct (a.user_id) as user_id FROM ( SELECT ct.buy_user_id as user_id FROM" +
                " yjd_creditor_transfer ct WHERE oktime BETWEEN ? " +
                " AND ? AND `status` = 3 UNION ALL SELECT i.user_id FROM yjd_invest i WHERE" +
                " addtime BETWEEN ? AND ? " +
                " UNION ALL SELECT w.user_id FROM yjd_wal_project_invest w WHERE create_time BETWEEN " +
                " ? AND ? ) a) d where a.user_id = d.user_id )";
        Object[] args = new Object[]{end_date, startTime, endTime, startTime, endTime, startTime, endTime};
        try {
            Integer sum = jt.queryForObject(QUERY_LOSS_USER_SUM_SQL, args, Integer.class);
            return sum;
        } catch (Exception e) {
            logger.error("流失用户数查询失败，处理sql:"+ QUERY_LOSS_USER_SUM_SQL + "错误信息:" + e);
        }
        return null;
    }

}
